package com.bringway.global.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.global.dao.BaseJdbcDao;
import com.bringway.global.dao.MenuDao;

import net.sf.json.JSONObject;

/*
* @author qjy
* @version 创建时间：2015年9月9日 下午2:36:09
* 类说明
*/
@Repository
public class MenuDaoImpl extends BaseJdbcDao implements MenuDao {
	
	Logger logger = Logger.getLogger(MenuDaoImpl.class);
	
	@Override
	public List<Map<String, Object>>  listMenuByParent(JSONObject params) {
		String nodeId = params.optString("nodeId");
		String roleId = params.optString("roleId");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" SELECT T1.ID,T1.NAME,T1.URL,T1.PARENT_ID, "); 
		sql.append(" CASE WHEN 1=1 THEN (                      "); 
		sql.append(" 	SELECT COUNT(*) CHILDCT FROM SYS_MENU T2 WHERE T2.PARENT_ID= T1.ID  "); 
		sql.append(" )END CHILDCT                                                            "); 
		sql.append(" FROM SYS_MENU T1 ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND T1.PARENT_ID = :PARENT_ID ");
		if(!"011500203".equals(params.optString("gyh"))){//超级管理员，不做菜单权限设置
			sql.append(" AND T1.ID IN (			");
			sql.append(" 	SELECT T2.MENU_ID FROM sys_menu_role T2	WHERE T2.ROLE_ID = :ROLE_ID ");
			sql.append(" )														");
		}
		paramMap.put("PARENT_ID", nodeId);
		paramMap.put("ROLE_ID", roleId);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public void saveMenu(JSONObject params) {
		String menuId = params.optString("menuId");
		String menuName = params.optString("menuName");
		String menuUrl = params.optString("menuUrl");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" UPDATE SYS_MENU ");
		sql.append(" SET NAME = :NAME ");
		sql.append(" ,URL = :URL ");
		sql.append(" WHERE ID = :ID ");
		paramMap.put("NAME", menuName);
		paramMap.put("URL", menuUrl);
		paramMap.put("ID", menuId);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public void saveMenuRole(String menuId, String roleId,String roleName) {
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" INSERT INTO sys_menu_role(MENU_ID,ROLE_ID,ROLE_NAME) ");
		sql.append(" VALUES(:MENU_ID,:ROLE_ID,:ROLE_NAME) ");
		paramMap.put("MENU_ID", menuId);
		paramMap.put("ROLE_ID", roleId);
		paramMap.put("ROLE_NAME", roleName);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public List<Map<String, Object>> queryMenuRoleList(JSONObject params) {
		String menuId=params.optString("menuId");
		if (!StringUtils.isNumeric(menuId)) {
			menuId=null;
		}
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" SELECT ID,MENU_ID,ROLE_ID,ROLE_NAME FROM sys_menu_role WHERE MENU_ID = :MENU_ID ");
		paramMap.put("MENU_ID", menuId);
		logger.info("queryMenuRoleList sql is "+sql.toString());
		logger.info("queryMenuRoleList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public void deleteMenuRole(JSONObject params) {
		String menuId = params.optString("menuId");
		StringBuffer sql = new StringBuffer();
		Map<String,String> paramMap = new HashMap<String,String>();
		sql.append(" DELETE FROM sys_menu_role ");
		sql.append(" WHERE MENU_ID = :MENU_ID ");
		paramMap.put("MENU_ID", menuId);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public Boolean testTransactional() {
		String sql = "insert into sys_menu(ID,NAME,URL) VALUES (1,'TEST','TEST.JSP')";
		Map<String,String> paramMap = new HashMap<String,String>();
		this.getNamedParameterJdbcTemplate().update(sql, paramMap);
		return true;
	}

	@Override
	public List<Map<String,Object>> listMenuByParent01(JSONObject params) {
		String nodeId = params.optString("nodeId");
		String roleId = params.optString("roleId");
		String gyh = params.optString("gyh");
		String id = params.optString("id");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		StringBuffer sql = new StringBuffer(" select t1.ID,t1.NAME,t1.URL,t1.PARENT_ID,case when 1=1 then (select count(*) CHILDCT from  sys_menu t2 where t2.PARENT_ID =t1.id ) end CHILDCT from  sys_menu t1 where id in(select (t2.MENU_ID) from  sys_user_menu t2 where t2.user_id = :id ) and t1.PARENT_ID = :nodeId order by CHILDCT desc,id asc ");
		paramsMap.put("nodeId", nodeId);
		paramsMap.put("roleId", roleId);
		paramsMap.put("gyh", gyh);
		paramsMap.put("id",id);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramsMap);
	}
	
	public Map<String, Map<String,Object>> listMenuMapByParent01(JSONObject params) {
		String nodeId = params.optString("nodeId");
		String roleId = params.optString("roleId");
		String gyh = params.optString("gyh");
		String id = params.optString("id");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		StringBuffer sql = new StringBuffer(" select t1.ID,t1.NAME,t1.URL,t1.PARENT_ID, t1.PARAMS,t1.ICON_NUM,ifnull(t1.ORDER_NUM,99) ORDER_NUM,case when 1=1 then (select count(*) CHILDCT from  sys_menu t2 where t2.PARENT_ID =t1.id ) end CHILDCT from  sys_menu t1 where id in(select (t2.MENU_ID) from  sys_user_menu t2 where t2.user_id = :id ) and t1.PARENT_ID = :nodeId order by t1.ORDER_NUM desc,id asc ");
		paramsMap.put("nodeId", nodeId);
		paramsMap.put("roleId", roleId);
		paramsMap.put("gyh", gyh);
		paramsMap.put("id",id);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramsMap, new ResultSetExtractor<Map<String, Map<String,Object>>>() {
			@Override
			public Map<String, Map<String,Object>> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, Map<String,Object>> result = new HashMap<>();
				while(rs.next()){
					Map<String,Object> pageInfo = new HashMap<>();
					pageInfo.put("ID", rs.getString("ID"));
					pageInfo.put("NAME", rs.getString("NAME"));
					pageInfo.put("URL", rs.getString("URL"));
					pageInfo.put("PARENT_ID", rs.getString("PARENT_ID"));
					pageInfo.put("PARAMS", rs.getString("PARAMS"));
					pageInfo.put("CHILDCT", rs.getString("CHILDCT"));
					pageInfo.put("ORDER_NUM", rs.getString("ORDER_NUM"));
					pageInfo.put("ICON_NUM", rs.getString("ICON_NUM"));
					result.put(rs.getString("ID"), pageInfo);
				}
				return result;
			}
		});
	}

	@Override
	public List<Map<String,Object>> listMenuByRole(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select t1.ID,t1.NAME,t1.URL,t1.PARENT_ID,case when 1=1 then (select count(*) CHILDCT from  sys_menu t2 where t2.PARENT_ID = t1.id ) end CHILDCT from sys_menu t1 where t1.ID in (select t3.MENU_ID from sys_menu_role t3 where t3.ROLE_ID = :roleId ) ");
		String nodeId = params.optString("nodeId");
		String roleId = params.optString("roleId");
		String gyh = params.optString("gyh");
		String id = params.optString("id");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		paramsMap.put("nodeId", nodeId);
		paramsMap.put("roleId", roleId);
		paramsMap.put("gyh", gyh);
		paramsMap.put("id", id);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramsMap);
	}
	@Override
	public Map<String, Map<String, Object>> listMenuMapByRole(JSONObject params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select t1.ID,t1.NAME,t1.URL,t1.PARENT_ID, t1.PARAMS,t1.ICON_NUM,ifnull(t1.ORDER_NUM,99) ORDER_NUM,case when 1=1 then (select count(*) CHILDCT from  sys_menu t2 where t2.PARENT_ID = t1.id ) end CHILDCT from sys_menu t1 where t1.ID in (select t3.MENU_ID from sys_menu_role t3 where t3.ROLE_ID = :roleId ) and t1.PARENT_ID = :nodeId ");
		String nodeId = params.optString("nodeId");
		String roleId = params.optString("roleId");
		String gyh = params.optString("gyh");
		String id = params.optString("id");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		paramsMap.put("nodeId", nodeId);
		paramsMap.put("roleId", roleId);
		paramsMap.put("gyh", gyh);
		paramsMap.put("id", id);
		return this.getNamedParameterJdbcTemplate().query(sql.toString(), paramsMap,new ResultSetExtractor<Map<String,Map<String,Object>>>() {
			Map<String,Map<String,Object>> result = new HashMap<String,Map<String,Object>>();
			@Override
			public Map<String, Map<String, Object>> extractData(ResultSet rs)
					throws SQLException, DataAccessException {
				while(rs.next()){
					Map<String,Object> map  =new HashMap<String,Object>();
					map.put("ID", rs.getString("ID"));
					map.put("NAME", rs.getString("NAME"));
					map.put("URL", rs.getString("URL"));
					map.put("PARENT_ID", rs.getString("PARENT_ID"));
					map.put("PARAMS", rs.getString("PARAMS"));
					map.put("CHILDCT", rs.getString("CHILDCT"));
					map.put("ORDER_NUM", rs.getString("ORDER_NUM"));
					map.put("ICON_NUM", rs.getString("ICON_NUM"));
					result.put(rs.getString("ID"), map);
				}
				return result;
			}
		});
	}

	@Override
	public List<Map<String, Object>>  getRoleListByMenuId(JSONObject parms) {
		String menuId = parms.optString("menuId");
		Map<String,Object> paramsMap = new HashMap<String,Object>();
		paramsMap.put("MENU_ID", menuId);
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from sys_menu_role where MENU_ID = :MENU_ID ");
		logger.info("getRoleListByMenuId sql is : " + sql.toString());
		logger.info("getRoleListByMenuId paramsMap is : " + paramsMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramsMap);
	}

	
	
}
